package com.ruoyi.common.utils.sql;

import cn.hutool.core.util.StrUtil;
import com.ruoyi.common.exception.UtilException;
import com.ruoyi.common.utils.StringUtils;

import java.util.Arrays;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * sql操作工具类
 */
public class SqlUtil
{
    /**
     * 定义常用的 sql关键字
     */
    public static String SQL_REGEX = "and |extractvalue|updatexml|exec |insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |or |+|user()";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value)
    {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value))
        {
            throw new UtilException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value)
    {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     */
    public static void filterKeyword(String value)
    {
        if (StringUtils.isEmpty(value))
        {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords)
        {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1)
            {
                throw new UtilException("参数存在SQL注入风险");
            }
        }
    }


    public static String formatSql(String sql, Map<String, Object> param) {
        sql = StrUtil.format(sql, param);

        // 定义匹配花括号中内容的正则表达式
        String regex = "\\{([^}]*)}";

        // 编译正则表达式
        Pattern pattern = Pattern.compile(regex);

        // 创建 Matcher 对象
        Matcher matcher = pattern.matcher(sql);

        // 查找匹配项
        while (matcher.find()) {
            // 获取匹配到的内容
            String match = matcher.group(1);
            String replacedMatch = match;
            // 进一步匹配 # 后的字段命名
            String fieldRegex = "#([\\w|\\[|\\]]+)";
            Pattern fieldPattern = Pattern.compile(fieldRegex);
            Matcher fieldMatcher = fieldPattern.matcher(match);

            // 查找字段命名
            while (fieldMatcher.find()) {
                // 输出匹配到的字段命名
                String fieldName = fieldMatcher.group(1);
                if (StrUtil.isBlank(fieldName) || !param.containsKey(fieldName) ||
                        (param.get(fieldName) instanceof CharSequence && StrUtil.isBlank((CharSequence) param.get(fieldName)))) {
                    replacedMatch = replacedMatch.replace(match, "");
                } else {
                    if (match.contains(" in ")) {
                        String inParam = Arrays.stream(((String) (param.get(fieldName))).split(",")).map(r -> "'" + r + "'").collect(Collectors.joining(","));
                        replacedMatch = replacedMatch.replace("#" + fieldName, inParam);
                    } else if (match.toLowerCase().contains(" like ")) {
                        replacedMatch = replacedMatch.replace("#" + fieldName, "'%" + param.get(fieldName) + "%'");
                    } else {
                        replacedMatch = replacedMatch.replace("#" + fieldName, "'" + param.get(fieldName) + "'");
                    }
                }
            }
            sql = sql.replace("{" + match + "}", replacedMatch);
        }
        return sql;
    }
}
